/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package jsf.bean.client;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.bean.SessionScoped;
import model.client.AccountDetailBeans;

/**
 *
 * @author Administrator
 */
@ManagedBean
@SessionScoped
public class MatrinonyBean_c {
    private int account_Id;
    private String full_Name;
    private String dateOfBirth;
    private String marital_Status;
    private String family_Details;

    public int getAccount_Id() {
        return account_Id;
    }

    public void setAccount_Id(int account_Id) {
        this.account_Id = account_Id;
    }

    public String getDateOfBirth() {
        return dateOfBirth;
    }

    public void setDateOfBirth(String dateOfBirth) {
        this.dateOfBirth = dateOfBirth;
    }

   

    

    public String getFamily_Details() {
        return family_Details;
    }

    public void setFamily_Details(String family_Details) {
        this.family_Details = family_Details;
    }

    public String getFull_Name() {
        return full_Name;
    }

    public void setFull_Name(String full_Name) {
        this.full_Name = full_Name;
    }

    public String getMarital_Status() {
        return marital_Status;
    }

    public void setMarital_Status(String marital_Status) {
        this.marital_Status = marital_Status;
    }
    
    
    /**
     * Creates a new instance of MatrinonyBean_c
     */
    
    Connection conn;
    public MatrinonyBean_c() throws SQLException, ClassNotFoundException {
        conn= JDBCUtil.MyConn.getConnection();
    }
    
//    SELECT Account_Details.Date_Of_Birth,Full_Name,
//GETDATE() AS[Today],
//DATEDIFF(YEAR,Date_Of_Birth, GETDATE())AS [AGE]
//from Account_Details
    
    public List<AccountDetailBeans> loadData() throws SQLException{
        String sql ="select Account_Id,Full_Name,Date_Of_Birth,Marital_Status,Family_Details, GETDATE() AS[Today],DATEDIFF(YEAR,Date_Of_Birth, GETDATE())AS [AGE] from Account_Details";
        PreparedStatement pst = conn.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        List<AccountDetailBeans> list= new ArrayList<AccountDetailBeans>();
        while(rs.next()){
            AccountDetailBeans a = new AccountDetailBeans();
            a.setAccount_Id(rs.getInt("Account_Id"));
            a.setFull_Name(rs.getNString("Full_Name"));
            a.setDateOfBirth(rs.getString("AGE"));
            a.setMarital_Status(rs.getString("Marital_Status"));
            a.setFamily_Details(rs.getString("Family_Details"));
            list.add(a);
        }
        
        return list;
    }
    
    public List<AccountDetailBeans> loadDetailByid() throws SQLException{
        String sql ="select Account_Id,Full_Name,Date_Of_Birth,Marital_Status,Family_Details, GETDATE() AS[Today],DATEDIFF(YEAR,Date_Of_Birth, GETDATE())AS [AGE] from Account_Details where Account_Id = ?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setInt(1, this.account_Id);
        ResultSet rs = pst.executeQuery();
        List<AccountDetailBeans> list= new ArrayList<AccountDetailBeans>();
        while(rs.next()){
            AccountDetailBeans a = new AccountDetailBeans();
            a.setAccount_Id(rs.getInt("Account_Id"));
            a.setFull_Name(rs.getNString("Full_Name"));
            a.setDateOfBirth(rs.getString("AGE"));
            a.setMarital_Status(rs.getString("Marital_Status"));
            a.setFamily_Details(rs.getString("Family_Details"));
            list.add(a);
        }
        
        return list;
    }
    
    public String gotoDetailPage(int account_Id){
        this.account_Id =account_Id;
        return "detail.xhtml?faces-redirect=true";
    }
    
    public List<AccountDetailBeans> searchByName() throws SQLException{        
        String sql ="select Account_Id,Full_Name,Date_Of_Birth,Marital_Status,Family_Details, GETDATE() AS[Today],DATEDIFF(YEAR,Date_Of_Birth, GETDATE())AS [AGE] from Account_Details where Full_Name like ?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, "%"+this.full_Name+"%");
        ResultSet rs = pst.executeQuery();
        List<AccountDetailBeans> list= new ArrayList<AccountDetailBeans>();
        while(rs.next()){
            AccountDetailBeans a = new AccountDetailBeans();
            a.setAccount_Id(rs.getInt("Account_Id"));
            a.setFull_Name(rs.getNString("Full_Name"));
            a.setDateOfBirth(rs.getString("AGE"));
            a.setMarital_Status(rs.getString("Marital_Status"));
            a.setFamily_Details(rs.getString("Family_Details"));
            list.add(a);
        }
        
        return list;
    }
    
    
    public String gotoHome(){
         return "index.xhtml?faces-redirect=true";
    }
    public String gotoRegister(){
         return "register.xhtml?faces-redirect=true";
    }
    public String gotoSearch(){
        
         return "search.xhtml?faces-redirect=true";
    }
    public String gotoService(){
         return "services.xhtml?faces-redirect=true";
    }
    public String gotoContact(){
         return "contact.xhtml?faces-redirect=true";
    }
    public String gotoUpdate(){
         return "updateProfile.xhtml?faces-redirect=true";
    }
    
    public void clear(){
        setFull_Name("");
        setDateOfBirth("");
        setFamily_Details("");
        setMarital_Status("");
    }
 }
